Microsoft Tabular Modeling Cookbook by Unknown

Author:Unknown
Language: eng
Format: epub
Publisher: Packt Publishing

Getting ready

This recipe extends the model that was built in the recipe titled Using the last non-empty function for stock data by creating a currency conversion based on the USD Gross Sales measure. The model includes the table of exchange rates (Currency Conversion) that shows the USD conversion from a currency. These rates appear for every day of history.

The sales data records its transactions in the source currency where the sale has taken place (see the Reseller Sales table). Therefore, if we wish to calculate a converted amount, we must first determine the USD value of line items by multiplying with the exchange rate applicable to the currency that the sale was recorded in, and then, dividing by the USD value for the target currency.

The Grouping by binning and sorting with ranks recipe included the creation of the additional column titled usd_gross_sales to the Resellers Sales table, which shows the value of the sale line in USD. In this recipe, we reproduce this field in a two-step process: firstly, we add the USD conversion rate (the value required to convert the lines value to USD) and then, we add the usd_gross_sales column.

While we could (and have) included this as a single step (see the definition of the usd_gross_sales column in the Grouping by binning and sorting with ranks recipe), it may make more sense to have a column value for the USD conversion in every row because it would be used (in a real environment) by multiple measures. For example, a conversion rate would be required for each standard measure such as Gross Sales, Net Sales, Cost of Goods Sold, and Profit. However, in this recipe we only work with Gross Sales.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.